﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.Data.SqlClient;

namespace For_FS_2013_1_User
{
    class Users
    {
        private const string dataBase = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Michael13\Documents\User_DB.mdf;Integrated Security=True;Connect Timeout=30";

        private SqlConnection sqlCn;
        private SqlCommand command = null;

        public Users()
        {
            OpenConnection();
        }

        public SqlConnection GetSqlConnection()
        {
            return sqlCn;
        }

        public void CloseConnection()
        {
            sqlCn.Close();
        }

        private void OpenConnection()
        {
            Console.WriteLine("Connection to the data base. . .");
            sqlCn = new SqlConnection(dataBase);
            sqlCn.Open();
            Console.WriteLine("Connected successfully!");
        }

        public SqlDataReader ExecuteSQLCommand(string sqlString)
        {
            command = new SqlCommand(sqlString, sqlCn);
            return command.ExecuteReader();
        }

        public bool IsAdmin(string login)
        {
            SqlDataReader dr = ExecuteSQLCommand("Select Admin from Users WHERE Login='"+login+"'");
            while (dr.Read())
            {
                if (dr["Admin"].ToString() == "True")
                {
                    dr.Close();
                    return true;
                }
            }
            dr.Close();
            return false;
        }

        public string AuthorizeAdmin(string login, string password)
        {
            password = new Cryptography().encryption(password);
            SqlDataReader dr = ExecuteSQLCommand("Select Login from Users WHERE Login='" + login + "' and Password='" + password + "' and Admin=1");
            while (dr.Read())
            {
                if (dr["Login"].ToString() == login.ToString())
                {
                    dr.Close();
                    ExecuteSQLCommand("UPDATE Users SET AdminControl=1 WHERE Login='" + login + "'").Close();
                    return login;
                }
            }
            return null;
        }

        public void LogoutAdmin(string login)
        {
            ExecuteSQLCommand("UPDATE Users SET AdminControl=0 WHERE Login='" + login + "'").Close();
        }

        public string AuthorizeUser(string login, string password)
        {
            password=new Cryptography().encryption(password);
            SqlDataReader dr = ExecuteSQLCommand("Select Login from Users WHERE Login='" + login + "' and Password='" + password + "'");
            while (dr.Read())
            {
                if (dr["Login"].ToString() == login.ToString())
                {
                    dr.Close();
                    ExecuteSQLCommand("UPDATE Users SET Online=1 WHERE Login='" + login + "'").Close();
                    return login;
                }
            }
            dr.Close();
            return null;
        }

        public void LogoutUser(string login)
        {
            ExecuteSQLCommand("UPDATE Users SET Online=0 WHERE Login='" + login + "'").Close();
        }

        public bool AddUser(string name, string surname, string login, string password, string confirmPassword, int ID)
        {
            if (!ContainsUser(login) && password == confirmPassword)
            {
                password = new Cryptography().encryption(password);
                ExecuteSQLCommand("INSERT into Users(ID, Name, Surname,Login, Password) values("
                                  + ID + ", '" + name + "','" + surname + "','" + login + "','" + password + "')").Close();
                return true;
            }
            return false;
        }

        private bool ContainsUser(string login)
        {
            SqlDataReader dr = ExecuteSQLCommand("Select Login from Users WHERE Login='" + login+"'");
            while (dr.Read())
                if (dr["Login"].ToString() == login)
                {
                    dr.Close();
                    return true;
                }
            dr.Close();
            return false;
        }

        public bool DelUser(string login)
        {
            ExecuteSQLCommand("DELETE from Users where login='" + login + "'").Close();
            if (!ContainsUser(login))
                return true;
            return false;
        }

        public bool ChangeUserPassword(string login, string oldPassword, string newPassword, string confirmPassword)
        {
            SqlDataReader dr = ExecuteSQLCommand("Select Login, Password from Users WHERE Login='" + login + "' and Password='" + oldPassword + "'");
            while (dr.Read())
                if (dr["Password"].ToString() == (new Cryptography().decryption(oldPassword)) && dr["Login"].ToString() == login && newPassword == confirmPassword)
                {
                    dr.Close();
                    ExecuteSQLCommand("UPDATE Users SET Password='" + (new Cryptography().encryption(newPassword)) + "' WHERE Login='" + login + "'").Close();
                    return true;
                }
            dr.Close();
            return false;
        }

        public bool ChangeUserRights(string login)
        {
            if (ContainsUser(login))
            {
                SqlDataReader dr = ExecuteSQLCommand("SELECT Admin from Users WHERE Login='" + login + "'");
                while (dr.Read())
                    if (dr["Admin"].ToString() == "False")
                    {
                        dr.Close();
                        ExecuteSQLCommand("UPDATE Users SET Admin=1 WHERE Login='" + login + "'").Close();
                    }
                    else
                    {
                        dr.Close();
                        ExecuteSQLCommand("UPDATE Users SET Admin=0 WHERE Login='" + login + "'").Close();
                    }
                return true;
            }
            return false;
        }

        public string ShowUser(string login)
        {
            if (ContainsUser(login))
            {
                SqlDataReader dr = ExecuteSQLCommand("SELECT * from Users where login='" + login + "'");
                string online;
                while (dr.Read())
                {
                    if (dr["Online"].ToString() == "False")
                        online = "Offline\n";
                    else
                        online = "Online\n";
                    string result = "ID:     " + dr["ID"].ToString() + "\nName:   " + dr["Name"].ToString()
                                     + "\nSurname:  " + dr["Surname"].ToString() + "\nLogin:  " + dr["Login"].ToString() + "\nAdmin: "
                                     + dr["Admin"].ToString() + "\nStatus:  " + online + "\n";
                    dr.Close();
                    return result;
                }
            }
            return "There is no such user in the user data base";
        }

        private string GetUserString(SqlDataReader dr)
        {
            string online;
            if (dr["Online"].ToString() == "False")
                online = "Offline\n";
            else
                online = "Online\n";
            return "ID:     " + dr["ID"].ToString() + "\nName:   " + dr["Name"].ToString()
                + "\nSurname:  " + dr["Surname"].ToString() + "\nLogin:  " + dr["Login"].ToString() + "\nAdmin: "
                + dr["Admin"].ToString() + "\nStatus:  " + online + "\n";
        }

        #region Show Users by criterias

        public HashSet<string> ShowAllUsers()
        {
            HashSet<string> result = new HashSet<string>();
            SqlDataReader dr = ExecuteSQLCommand("SELECT * from Users");
            while (dr.Read())
            {
                result.Add(GetUserString(dr));
            }
            dr.Close();
            return result;
        }

        public HashSet<string> ShowOnlineUsers()
        {
            HashSet<string> result = new HashSet<string>();
            SqlDataReader dr = ExecuteSQLCommand("SELECT * from Users WHERE Online=1");
            while (dr.Read())
            {
                result.Add(GetUserString(dr));
            }
            dr.Close();
            return result;
        }

        public HashSet<string> ShowOfflineUsers()
        {
            HashSet<string> result = new HashSet<string>();
            SqlDataReader dr = ExecuteSQLCommand("SELECT * from Users WHERE Online=0");
            while (dr.Read())
            {
                result.Add(GetUserString(dr));
            }
            dr.Close();
            return result;
        }
        #endregion
    }
}
